USE [influenza]
GO

/****** Object:  StoredProcedure [dbo].[sp_busquedaNoRegistrados]    Script Date: 01/08/2010 14:32:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ===========================================================================================
-- Autor: Miguel Angel Chavez
-- Creado: Enero 8, 2010
-- Descripción:	Este stored procedure regresa datos de una busqueda a la tabla de noREgistrados
-- ===========================================================================================
CREATE PROCEDURE [dbo].[sp_busquedaNoRegistrados] 
@param varchar(8000)
AS
BEGIN
	SET NOCOUNT ON;
    declare
      @qry varchar(max),
      @cPaterno varchar(50),
      @cMaterno varchar(50),
      @cNombre varchar(50),
      @id_entidad varchar(3)
      
      set @cPaterno=influenza.dbo.fnSplit(@param,'|',1);
      set @cMaterno=influenza.dbo.fnSplit(@param,'|',2);
      set @cNombre=influenza.dbo.fnSplit(@param,'|',3);
      set @id_entidad=influenza.dbo.fnSplit(@param,'|',4);

      
set @qry='select (cNombre+'' ''+cPaterno+'' ''+cMaterno) as Paciente, (e.nombre) as Estado,
CONVERT(varchar(10),a.fRegistro,103)as Fecha, (a.nFolio) as Folio
from dat_NoRegistrados a 
inner join Catalogos.dbo.entidad e on (a.nEstado=e.entidad)
inner join usuarios u on (a.id_usuario=u.id_principal)
inner join Unidades_todas t on (u.id_unidad = t.id_unidad)
Where '

if(@cPaterno<>'')
  begin
	set @qry=@qry+'cPaterno like'+'''%'+@cPaterno+'%'''
  end
 
if ((@cPaterno='') and (@cMaterno<>''))
  begin
     set @qry=@qry+'cMaterno like'+'''%'+@cMaterno+'%'''
  end
else 
  begin
    if ((@cPaterno<>'')and(@cMaterno<>''))
    begin
     set @qry=@qry+' and cMaterno like'+'''%'+@cMaterno+'%'''
    end
  end
  
if ((@cPaterno='') and (@cMaterno='') and (@cNombre<>''))
  begin
     set @qry=@qry+'cNombre like'+'''%'+@cNombre+'%'''
  end
else 
  begin
    if (((@cPaterno<>'')or(@cMaterno<>''))and (@cNombre<>''))
     set @qry=@qry+' and cNombre like'+'''%'+@cNombre+'%'''
  end
  
  if((@id_entidad<>'0')or(@id_entidad<>''))
   begin
     set @qry=@qry+' and t.id_entidad='''+@id_entidad+''''
   end
  exec(@qry)
END

GO

